package com.hs.hlw.credit.system.data.util;

import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.hs.hlw.credit.system.common.enumeration.FieldDataType_DM;
import com.hs.hlw.credit.system.common.enumeration.LogicOperator_DM;
import com.hs.hlw.credit.system.common.util.DateTimeUtils;
import com.hs.hlw.credit.system.common.util.JsonUtil;
import com.hs.hlw.credit.system.common.util.StringUtil;
import com.hs.hlw.credit.system.data.DataUtils;
import com.hs.hlw.credit.system.data.mybatis.Pageable;
import com.hs.hlw.credit.system.data.mybatis.Sort;
import com.hs.hlw.credit.system.data.primary.model.SubmissionFormField;
import com.hualongdata.exception.BusinessException;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.ConcurrentHashMap;

/**
 * DbUtil_DM
 *
 * @author zhb
 * @date 2018/11/21
 */
public class DbUtil_DM implements DbUtil {
    private static final Map<String, DbUtil_DM> DATA_SOURCE_MAP = new ConcurrentHashMap<>();
    private BasicDataSource dataSource;
    private static final Logger LOGGER = LoggerFactory.getLogger(DbUtil_DM.class);

    private DbUtil_DM(String url, String userName, String password) {
        dataSource = new BasicDataSource();
        dataSource.setDriverClassName("dm.jdbc.driver.DmDriver");
        dataSource.setInitialSize(10);
        dataSource.setMaxIdle(20);
        dataSource.setMinIdle(5);
        dataSource.setMaxTotal(50);
        dataSource.setMaxWaitMillis(8000L);
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
    }

    /**
     * 获取实例
     *
     * @param url
     * @param userName
     * @param password
     * @return
     */
    public static DbUtil_DM getInstance(String url, String userName, String password) {
        if (DATA_SOURCE_MAP.containsKey(url)) {
            return DATA_SOURCE_MAP.get(url);
        } else {
            DbUtil_DM dbUtil = new DbUtil_DM(url, userName, password);
            DATA_SOURCE_MAP.put(url, dbUtil);
            return dbUtil;
        }
    }

    /**
     * 删除
     * @param tableName
     * @param whereList
     * @throws Exception
     */
    public void delete(String tableName, List<SubmissionFormField> whereList) throws BusinessException {
        if (whereList == null || whereList.isEmpty()) {
            throw new BusinessException("未指定删除条件");
        }
        StringBuilder sql = new StringBuilder("delete from " + tableName + " where ");
        for (int i = 0; i < whereList.size(); i++) {
            LogicOperator_DM logicOperator = LogicOperator_DM.getLogicOperatorByCode(whereList.get(i).getLogicOperator());
            String condition = whereList.get(i).getFieldCode() + logicOperator.detail;
            sql.append(i == 0 ? condition : " " + whereList.get(i).getLogicRelationship() + " " + condition);
        }
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection(true);
            Map<String, Object> map = new HashMap<>();
            if (whereList != null && !whereList.isEmpty()) {
                for (int i = 0; i < whereList.size(); i++) {
                    map.put(whereList.get(i).getFieldCode(), whereList.get(i).getFieldValue());
                }
            }
            JsonObject data = JsonUtil.objToJson(map);
            statement = connection.prepareStatement(sql.toString());
            setValue(whereList, data, statement);
            statement.executeUpdate();
        } catch (Exception e) {
            LOGGER.error("删除失败", e);
            throw new BusinessException("删除失败");
        } finally {
            close(connection, statement, null);
        }
    }

    /**
     * 保存数据
     *
     * @param tableName
     * @param fieldList
     * @param data
     */
    public void insert(String tableName, List<SubmissionFormField> fieldList, JsonArray data) throws Exception {
        if (fieldList == null || fieldList.isEmpty()) {
            return;
        }
        if (data == null && data.size() == 0) {
            return;
        }

        Connection connection = null;
        try {
            // 获取一个不自动提交的连接
            connection = getConnection(false);

            // 保存数据
            StringBuilder sql = new StringBuilder("insert into " + tableName + " (");
            for (int i = 0; i < fieldList.size(); i++) {
                SubmissionFormField field = fieldList.get(i);
                sql.append(i != 0 ? ", " + field.getFieldCode().toUpperCase() : field.getFieldCode().toUpperCase());
            }
            sql.append(") values (");
            for (int i = 0; i < fieldList.size(); i++) {
                sql.append(i != 0 ? ", ?" : "?");
            }
            sql.append(")");
            for (int i = 0; i < data.size(); i++) {
                PreparedStatement preparedStatement = null;
                try {
                    preparedStatement = connection.prepareStatement(sql.toString());
                    JsonObject json = data.get(i).getAsJsonObject();
                    setValue(fieldList, json, preparedStatement);
                    preparedStatement.executeUpdate();
                } catch (Exception e2) {
                    throw e2;
                } finally {
                    close(null, preparedStatement, null);
                }
            }
            connection.commit();
        } catch (Exception e) {
            LOGGER.error("数据库访问异常", e);
            if (connection != null) {
                connection.rollback();
            }
            throw e;
        } finally {
            close(connection, null, null);
        }
    }


    /**
     * 更新
     * @param tableName
     * @param fieldList
     * @param data
     * @param where
     */
    public void update(String tableName, List<SubmissionFormField> fieldList, JsonObject data, String where) throws Exception {
        if (fieldList == null || fieldList.isEmpty()) {
            return;
        }
        if (data == null) {
            return;
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取一个不自动提交的连接
            connection = getConnection(false);

            // 保存数据
            StringBuilder sql = new StringBuilder("update " + tableName + " set ");
            for (int i = 0; i < fieldList.size(); i++) {
                SubmissionFormField field = fieldList.get(i);
                sql.append(i != 0 ? ", " + field.getFieldCode().toUpperCase() + " = ?" : field.getFieldCode().toUpperCase() + " = ?");
            }
            if (StringUtils.isNotBlank(where)) {
                sql.append(" where " + where);
            }
            preparedStatement = connection.prepareStatement(sql.toString());
            setValue(fieldList, data, preparedStatement);
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (Exception e) {
            connection.rollback();
            throw e;
        } finally {
            close(connection, preparedStatement, null);
        }
    }

    /**
     * 根据sql查询数据
     * @param sql
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> selectBySql(String sql) throws SQLException {
        Connection connection = getConnection(true);
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(sql.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int colCont = metaData.getColumnCount();
                Map<String, Object> map = new HashMap<>();
                list.add(map);
                for (int i = 0; i < colCont; i++) {
                    String key = metaData.getColumnLabel(i + 1);
                    int columnType = metaData.getColumnType(i + 1);
                    JDBCType jdbcType = DataUtils.getJDBCTypeByColumnType(columnType);
                    switch (jdbcType) {
                        case DATE:
                        case TIMESTAMP:
                            map.put(key.toLowerCase(), resultSet.getDate(key));
                            break;
                        default:
                            map.put(key.toLowerCase(), resultSet.getString(key));
                            break;
                    }
                }
            }
        } catch (Exception e) {
            throw e;
        } finally {
            close(connection, preparedStatement, resultSet);
        }

        return list;
    }

    /**
     * 分页查询
     * @param tableName
     * @param fieldList
     * @param whereList
     * @param pageable
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> page(String tableName, List<SubmissionFormField> fieldList, List<SubmissionFormField> whereList, Pageable pageable) throws BusinessException {
        // 拼装sql语句
        StringBuilder sql = new StringBuilder("select ");
        if (fieldList == null || fieldList.isEmpty()) {
            sql.append("*");
        } else {
            for (int i = 0; i < fieldList.size(); i++) {
                sql.append(i == 0 ? fieldList.get(i).getFieldCode() : ", " + fieldList.get(i).getFieldCode());
            }
        }
        sql.append(" from " + tableName);
        if (whereList != null && !whereList.isEmpty()) {
            sql.append(" where ");
            for (int i = 0; i < whereList.size(); i++) {
                LogicOperator_DM logicOperator = LogicOperator_DM.getLogicOperatorByCode(whereList.get(i).getLogicOperator());
                String condition = whereList.get(i).getFieldCode() + logicOperator.detail;
                sql.append(i == 0 ? condition : " " + whereList.get(i).getLogicRelationship() + " " + condition);
            }
        }


        Connection connection = null;
        PreparedStatement countStatement = null;
        PreparedStatement pageStatement = null;
        ResultSet resultSet1 = null;
        ResultSet resultSet2 = null;
        List<Map<String, Object>> dataList = new ArrayList<>();
        try {
            // 获取总记录数
            connection = getConnection(true);
            Map<String, Object> map = new HashMap<>();
            if (whereList != null && !whereList.isEmpty()) {
                for (int i = 0; i < whereList.size(); i++) {
                    map.put(whereList.get(i).getFieldCode(), whereList.get(i).getFieldValue());
                }
            }
            JsonObject data = JsonUtil.objToJson(map);
            String countSql = "select count(*) totalRecord from (" + sql.toString() + ")";
            countStatement = connection.prepareStatement(countSql);
            setValue(whereList, data, countStatement);
            resultSet1 = countStatement.executeQuery();
            if (resultSet1.next()) {
                int totalRecord = resultSet1.getInt("totalRecord");
                pageable.setTotalElements(totalRecord);
            }

            // 获取分页数据
            List<Sort> sorts = pageable.getSorts();
            if (sorts != null && !sorts.isEmpty()) {
                sql.append(" order by ");
                for (int i = 0; i < sorts.size(); i++) {
                    if (i == 0) {
                        sql.append(sorts.get(i).getOrderField());
                    } else {
                        sql.append(", " + sorts.get(i).getOrderField());
                    }
                    switch (sorts.get(i).getDirection()) {
                        case ASC:
                            sql.append(" asc");
                            break;
                        case DESC:
                            sql.append(" desc");
                            break;
                        default:
                            break;
                    }
                }
            }
            String pageSql = sql.append(" limit " + ((pageable.getPage() - 1) * pageable.getSize()) + ", " + pageable.getSize()).toString();
            pageStatement = connection.prepareStatement(pageSql);
            setValue(whereList, data, pageStatement);
            resultSet2 = pageStatement.executeQuery();
            while (resultSet2.next()) {
                Map<String, Object> dataMap = new HashMap<>();
                dataList.add(dataMap);
                for (SubmissionFormField field : fieldList) {
                    FieldDataType_DM dataType = FieldDataType_DM.getTypeByCode(field.getFieldDataType());
                    switch (dataType) {
                        case DATE:
                        case DATETIME:
                        case TIMESTAMP:
                            JsonObject jsonObject = JsonUtil.strToJson(field.getRuleCfg());
                            if (jsonObject == null || !jsonObject.has("dateFormat")) {
                                throw new BusinessException("字段规则配置有误{dataType: " + dataType.code + ", fieldCode: " + field.getFieldCode() + "}");
                            }
                            String dataFormat = jsonObject.get("dateFormat").getAsString();
                            Date date = resultSet2.getDate(field.getFieldCode());
                            map.put(field.getFieldCode(), date != null ? DateTimeUtils.dateToString(date, dataFormat) : null);
                            break;
                        default:
                            dataMap.put(field.getFieldCode(), resultSet2.getString(field.getFieldCode()));
                            break;
                    }
                }
            }
        } catch (Exception e) {
            LOGGER.error("查询失败", e);
            throw new BusinessException("查询失败");
        } finally {
            close(null, countStatement, resultSet1);
            close(null, pageStatement, resultSet2);
            close(connection, null, null);
        }

        return dataList;
    }



    /**
     * 设置值
     *
     * @param fieldList
     * @param data
     * @param preparedStatement
     */
    private void setValue(List<SubmissionFormField> fieldList, JsonObject data, PreparedStatement preparedStatement) throws Exception {
        if (fieldList == null || fieldList.isEmpty()) {
            return;
        }
        for (int i = 0; i < fieldList.size(); i++) {
            SubmissionFormField field = fieldList.get(i);
            FieldDataType_DM dataType = FieldDataType_DM.getTypeByCode(field.getFieldDataType());
            String filedValue = data.has(field.getFieldCode()) ? data.get(field.getFieldCode()).getAsString() : null;
            filedValue = StringUtils.isNotBlank(filedValue) ? filedValue : null;
            switch (dataType) {
                case CHAR:
                case VARCHAR:
                case VARCHAR2:
                case TEXT:
                case CLOB:
                case BIGINT:
                case DECIMAL:
                case TINYINT:
                case INT:
                case INTEGER:
                case NUMBER:
                case DOUBLE:
                case FLOAT:
                    preparedStatement.setString(i + 1, filedValue);
                    break;

                case DATE:
                case TIMESTAMP:
                case DATETIME:
                    JsonObject jsonObject = JsonUtil.strToJson(field.getRuleCfg());
                    if (jsonObject == null || !jsonObject.has("dateFormat")) {
                        throw new BusinessException("字段规则配置有误{dataType: " + dataType.code + ", fieldCode: " + field.getFieldCode() + "}");
                    }
                    String dataFormat = jsonObject.get("dateFormat").getAsString();
                    Date date = filedValue != null ? DateTimeUtils.stringToDate(filedValue, dataFormat) : null;
                    String dateStr = date != null ? DateTimeUtils.dateToString(date, DateTimeUtils.YYYY_MM_DD_HH_MM_SS) : null;
                    preparedStatement.setString(i + 1, dateStr);
                    break;

                default:
                    throw new BusinessException("未知的字段类型{dataType: " + dataType.code + ", fieldCode: " + field.getFieldCode() + "}");
            }
        }
    }

    /**
     * 从连接池中获取一个连接
     *
     * @param autoCommit
     * @return
     * @throws SQLException
     */
    private Connection getConnection(boolean autoCommit) throws SQLException {
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(autoCommit);
        return connection;
    }

    /**
     * 关闭连接
     *
     * @param connection
     * @param statement
     */
    private void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                dataSource.invalidateConnection(connection);
            }
        } catch (SQLException e) {
            // TODO
        }
    }

    private static void test1() {
        String url = "jdbc:dm://192.168.32.104:5236/USER_TEST?characterEncoding=utf8&useSSL=true";
        String userName = "USER_TEST";
        String password = "hldata.2016";
        DbUtil dbUtil = getInstance(url, userName, password);

        List<SubmissionFormField> fieldList = new ArrayList<>();
        for (int i = 1; i < 9; i++) {
            SubmissionFormField field = new SubmissionFormField();
            fieldList.add(field);
            field.setFieldCode("COLUMN_" + i);
        }
        fieldList.get(0).setFieldDataType("VARCHAR2");
        fieldList.get(1).setFieldDataType("DECIMAL");
        fieldList.get(2).setFieldDataType("NUMBER");
        fieldList.get(3).setFieldDataType("INTEGER");
        fieldList.get(4).setFieldDataType("FLOAT");
        fieldList.get(5).setFieldDataType("BIGINT");
        fieldList.get(6).setFieldDataType("DATE");
        Map<String, Object> map = new HashMap<>();
        map.put("dateFormat", "yyyy-MM-dd");
        fieldList.get(6).setRuleCfg(JsonUtil.toJson(map));
        fieldList.get(7).setFieldDataType("DATETIME");
        map = new HashMap<>();
        map.put("dateFormat", "yyyy-MM-dd HH:mm:ss");
        fieldList.get(7).setRuleCfg(JsonUtil.toJson(map));

        long time = System.currentTimeMillis();
        try {
            JsonArray array = new JsonArray();
            for (int i = 0; i < 1; i++) {
                JsonObject json = new JsonObject();
                array.add(json);
                json.addProperty("COLUMN_1", "1");
                json.addProperty("COLUMN_2", "1");
                json.addProperty("COLUMN_3", "1.01");
                json.addProperty("COLUMN_4", "2.00");
                json.addProperty("COLUMN_5", "1");
                json.addProperty("COLUMN_6", "1");
                json.addProperty("COLUMN_7", "2008-10-11");
                json.addProperty("COLUMN_8", "2008-10-11 22:11:11");
            }
            dbUtil.insert("TEST", fieldList, array);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("执行时间" + (System.currentTimeMillis() - time));
    }

    private static void test2() {
        try {
            String url = "jdbc:dm://192.168.32.104:5236/USER_TEST?characterEncoding=utf8&useSSL=true";
            String userName = "USER_TEST";
            String password = "hldata.2016";
            DbUtil dbUtil = getInstance(url, userName, password);
            List<Map<String, Object>> list = dbUtil.selectBySql("select COLUMN_8 as date222 from \"USER_TEST\".\"TEST\"");
            System.out.println(JsonUtil.toJson(list));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        test1();
        //test2();
    }
}